﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace xlsx2const
{
    class EntityDefinition
    {
        /*
         * ================================================================
         * CREATE TABLE fms_pr_plan (
         *  is_onetime_bank_account VARCHAR2(1) DEFAULT '0' NOT NULL,
         * );
         * ================================================================
         * COMMENT ON TABLE fms_pr_w_eb_data IS 'EBデータワークテーブル';
         * COMMENT ON COLUMN fms_pr_w_eb_data.id IS 'ID';
         * ================================================================
         **/

         /** テーブル論理名 */
        private static string TABLE_COMMENT         = "H1";
        /** テーブル物理名 */
        private static string TABLE_NAME            = "H2";
        /** 最初のカラム論理名 */
        private static string COLOMUN_COMMENT       = "B";
        /** 最初のカラム物理名 */
        private static string COLOMUN_NAME          = "G";
        /** 最初のカラムNN */
        private static string COLOMUN_NOT_NULL      = "U";
        /** 最初のカラムデータ型 */
        private static string COLOMUN_TYPE          = "AB";
        /** 最初のカラム型大きさ */
        private static string COLOMUN_SIZE          = "AF";
        /** 最初のカラム初期値 */
        private static string COLOMUN_DEFAULT_VALUE = "AJ";
        private static int FIRST_COLOMUN_LINE_NUM   = 9;

        public static string getEntityString(string fullPath)
        {
            // 入力チェック
            if (fullPath == null) {return "";}
            
            // xlsxドキュメントを読み込み
            SpreadsheetDocument document = ReaderCommon.getDoc(fullPath);

            // WorkbookPartを取得
            WorkbookPart wbPart = ReaderCommon.getWbPart(document);

            // シートのリストを取得
            List<string> sList = ReaderCommon.getSheetList(document, wbPart);
            // エンティティ定義書のシート数は1つしか無いはず
            int sCnt = sList.Count;
            if (sCnt != 1)
            {
                if (sCnt == 0)
                {
                    // シートが取得できない旨のメッセージを表示したい
                    // TODO★
                    return "";
                }
                else
                {
                    // シートが2枚以上存在する旨のメッセージを表示したい
                    return "";
                }
            }

            // Sheet1という名前のオブジェクトを取得
            Sheet targetSheet = 
                wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sList[0]).FirstOrDefault();
            if (targetSheet == null)
            {
                return "";
            }

            // SheetからIDを取得し、WorksheetPartを得る
            WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(targetSheet.Id));

            /*
             * ================================================================
             * CREATE TABLE fms_pr_plan (
             *  is_onetime_bank_account VARCHAR2(1) DEFAULT '0' NOT NULL,
             * );
             * ================================================================
             * COMMENT ON TABLE fms_pr_w_eb_data IS 'EBデータワークテーブル';
             * COMMENT ON COLUMN fms_pr_w_eb_data.id IS 'ID';
             * ================================================================
             **/

            StringBuilder sql = new StringBuilder("");
            StringBuilder comment = new StringBuilder("");

            string tableCmnt = ReaderCommon.getCellValue(wsPart, TABLE_COMMENT);
            string tableNm   = ReaderCommon.getCellValue(wsPart, TABLE_NAME);
            bool isContinue = true;
            int rownum = FIRST_COLOMUN_LINE_NUM;
            string clmNm = "";  
            string clmCm = "";  
            string clmNn = "";  
            string clmDf = "";
            string clmTp = "";
            string clmSz = "";

            while(isContinue) {
                // カラム物理名の取得
                clmNm = ReaderCommon.getCellValue(wsPart, COLOMUN_NAME + rownum.ToString());
                if (ReaderCommon.isEmpty(clmNm))
                {
                    // ここに入るときは""の時
                    break;
                }

                // カラムコメントの取得
                clmCm = ReaderCommon.getCellValue(wsPart, COLOMUN_COMMENT + rownum.ToString());
                // Not Null制約の取得
                clmNn = ReaderCommon.getCellValue(wsPart, COLOMUN_NOT_NULL + rownum.ToString());
                // デフォルト値の取得
                clmDf = ReaderCommon.getCellValue(wsPart, COLOMUN_DEFAULT_VALUE + rownum.ToString());
                // カラムの方を取得
                clmTp = ReaderCommon.getCellValue(wsPart, COLOMUN_TYPE + rownum.ToString());
                // カラムの値を取得
                clmSz = ReaderCommon.getCellValue(wsPart, COLOMUN_SIZE + rownum.ToString());

                //Console.WriteLine("カラム：" + clmNm + "（"+ clmCm +"）- NotNull=" + clmNn + "　デフォルト値：" + clmDf);

                if (rownum == FIRST_COLOMUN_LINE_NUM)
                {
                    sql.Append("CREATE TABLE " + tableNm + " (\n");
                    sql.Append("    "
                        + clmNm
                        + getColType(clmTp, clmSz)
                        + getDefaultValue(clmDf)
                        + getNotNull(clmNn) + "\n");

                    comment.Append("COMMENT ON TABLE  " + tableNm + " IS '" + tableCmnt + "';\n");
                    comment.Append("COMMENT ON COLUMN " + tableNm + "." + clmNm + " IS '" + clmCm + "';\n");
                }
                else
                {
                    sql.Append("   ,"
                        + clmNm
                        + getColType(clmTp, clmSz)
                        + getDefaultValue(clmDf)
                        + getNotNull(clmNn) + "\n");

                    comment.Append("COMMENT ON COLUMN " + tableNm + "." + clmNm + " IS '" + clmCm + "';\n");
                }
                rownum++;
            }

            // ファイルを閉じる
            document.Close();

            sql.Append(");\n\n");

            return sql.ToString() + comment.ToString() + "\n";
        }

        private static string getColType(string type, string size)
        {
            if (ReaderCommon.isEmpty(size))
            {
                return " " + type;
            }
            return " " + type + "(" + size + ")";
        }

        private static string getDefaultValue(string def)
        {
            if (ReaderCommon.isEmpty(def))
            {
                return "";
            }
            else if (def.ToUpper().Equals("NUMBER"))
            {
                return " DEFAULT " + def;
            }
            return " DEFAULT '" + def + "'";
        }

        private static string getNotNull(string nn)
        {
            if (ReaderCommon.isEmpty(nn))
            {
                return "";
            }
            return " NOT NULL";
        }
    }
}
